package com.szholly.pro.watergas_server.ps.service;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelCreate {  
	  
	   public  HSSFWorkbook wb = null;  
	  
	   public  HSSFSheet sheet = null;  
	  
	   public  HSSFDataFormat format = null;  
	  
	   public  HSSFRow hdRow = null;  
	  
	    int listlength = 0;  
	  
	    /**  
	     * 设置工作表的格式  
	     *   
	     * @param sheetName  
	     */  
	    public ExcelCreate() {  
	        wb = new HSSFWorkbook();  
	    }  
	  
	    public void createSheet(String sheetName) {  
	        sheet = wb.createSheet(sheetName);  
	        format = wb.createDataFormat();  
	        hdRow = sheet.createRow(0);  
	        sheet.setDefaultRowHeightInPoints(60);  
	        sheet.setDefaultColumnWidth(12);  
	    }  
	    /**  
	     * 表头数据  
	     *   
	     * @throws Exception  
	     */  
	    public void addHeader(List rowvalues, boolean isFilter,int rowNum) throws Exception {  
	        listlength = rowvalues.size();  
	        // 设置字体  
	        HSSFFont workFont = wb.createFont();  
	        workFont.setFontName("微软雅黑");  
	        workFont.setFontHeightInPoints((short) 14);  
	        workFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	        // 表头样式及背景色  
	        HSSFCellStyle hdStyle = wb.createCellStyle();  
	        hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
	        hdStyle.setBottomBorderColor(HSSFColor.BLACK.index);  
	        hdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
	        hdStyle.setLeftBorderColor(HSSFColor.BLACK.index);  
	        hdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
	        hdStyle.setRightBorderColor(HSSFColor.BLACK.index);  
	        hdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);  
	        hdStyle.setTopBorderColor(HSSFColor.BLACK.index);  
	        hdStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	        hdStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);  
	        hdStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
	        hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
	        hdStyle.setFont(workFont);  
	  
	        String[] title = new String[rowvalues.size()];  
	        for (int i = 0; i < rowvalues.size(); i++) {  
	            title[i] = (String) rowvalues.get(i);  
	        }  
	        HSSFRow dtRow = sheet.createRow((rowNum));  
	        //设置行高
	        dtRow.setHeightInPoints(30);
	        if (isFilter == true) {  
	        	//设置第一列的宽度
	        	sheet.setColumnWidth(0, 35 * 256);
	            for (int i = 1; i < title.length; i++) {  
	                HSSFCell cell1 = hdRow.createCell(i);  
	                sheet.setColumnWidth(i, 25 * 256);
	                HSSFRichTextString value = new HSSFRichTextString(title[i]);  
	                cell1.setCellValue(value);  
	                cell1.setCellStyle(hdStyle);  
	            }  
	        } else {  
	        	sheet.setColumnWidth(0, 35 * 256);
	            for (int i = 1; i < title.length; i++) {  
	                HSSFCell cell2 = dtRow.createCell(i);  
	                sheet.setColumnWidth(i, 25 * 256);
	                HSSFRichTextString value2 = new HSSFRichTextString(title[i]);  
	                cell2.setCellValue(value2);  
	            }  
	        }  
	    }  
	    /**  
	     * 添加一行  
	     */  
	    int s = 0;  
	  
	    public void addRow(List rowvalues,int length) {  
	        HSSFRow dtRow = sheet.createRow(s++);  
        	HSSFCellStyle cellSty = wb.createCellStyle();
	        if(s==1) {
	        	//第一行进行合并
	        	sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 2));
	        }
	        if(s==2) {
	        	cellSty.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 
	        	cellSty.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 
	        	cellSty.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 
	        	cellSty.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
	        	cellSty.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
	        	cellSty.setFillPattern(CellStyle.SOLID_FOREGROUND);
	        	
	        }
	        dtRow.setHeightInPoints(20);
	        sheet.setColumnWidth(0, 35 * 256);
	        for(int i=1;i<length+1;i++) {
		        sheet.setColumnWidth(i, 20 * 256);
	        }
	        DataFormat format = wb.createDataFormat();  
	          
	        HSSFCellStyle dtStyle = wb.createCellStyle();  
	        dtStyle.setDataFormat(format.getFormat("text"));  
	        dtStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
	        dtStyle.setBottomBorderColor(HSSFColor.BLACK.index);  
	        dtStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
	        dtStyle.setLeftBorderColor(HSSFColor.BLACK.index);  
	        dtStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
	        dtStyle.setRightBorderColor(HSSFColor.BLACK.index);  
	        dtStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);  
	        dtStyle.setTopBorderColor(HSSFColor.BLACK.index);  
	        dtStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);  
	        cellSty.setFillPattern(CellStyle.SOLID_FOREGROUND);
	          
	        for (int j = 0; j < rowvalues.size(); j++) {  
	            Object cell_data = rowvalues.get(j);  
	            HSSFCell cell = dtRow.createCell(j);  
	            // 正文格式  
                cell.setCellValue((String)cell_data);   
                if(s==2) {
                	cell.setCellStyle(cellSty);  
                } else {
	                cell.setCellStyle(dtStyle);  
                }        
	        }  
	    }      
	    /**  
	     * 具体文件生成的文件  
	     *   
	     * @param file  
	     * @throws Exception  
	     */  
	    public void exportExcel(File file) throws Exception {  
	        FileOutputStream fileOut = new FileOutputStream(file);  
	        wb.write(fileOut);  
	        fileOut.close();  
	    }  
	  
	    /**  
	     * 具体文件生成的文件  
	     *   
	     * @param file  
	     * @throws Exception  
	     */  
	    public void exportExcel(OutputStream outputstream) throws Exception {  
	        BufferedOutputStream buffout = new BufferedOutputStream(outputstream);  
	        wb.write(buffout);  
	        buffout.flush();  
	        buffout.close();  
	    }  
}